﻿/*********************************************************************************************************************
VanguardDocumentation, View Level2Entities

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'V'
			and o.name = 'Level2Entities'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create view VanguardDocumentation.Level2Entities
		as select ''View Level2Entities is not implemented yet!'' as Error
	';
	execute (@SqlCommand);
end;
GO

alter view VanguardDocumentation.Level2Entities
	/*****************************************************************************************************************
	Lists all entities on level 2 for extended properties.
	
	The column SearchOrder is intended for when the user only gives a name (no type). The order is firstly by level
	1 entity (same as in view Level1Entities):
	TABLE, VIEW, FUNCTION, PROCEDURE, QUEUE
	and then by level 2:
	PARAMETER, COLUMN, CONSTRAINT, TRIGGER, INDEX, EVENT NOTIFICATION
	*****************************************************************************************************************/
as
select 1 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(t.[schema_id]) collate database_default as Level0Name,
	'TABLE' as Level1Type, t.name collate database_default as Level1Name,
	'COLUMN' as Level2Type, c.name collate database_default as Level2Name
from sys.tables as t
	inner join sys.columns as c on (t.[object_id] = c.[object_id])

union all
select 2 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(t.[schema_id]) collate database_default as Level0Name,
	'TABLE' as Level1Type, t.name collate database_default as Level1Name,
	'CONSTRAINT' as Level2Type, c.name collate database_default as Level2Name
from sys.tables as t
	inner join sys.objects as c on (t.[object_id] = c.parent_object_id)
where c.[type] in ('C', 'D', 'F', 'PK', 'UQ')

union all
select 3 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(t.[schema_id]) collate database_default as Level0Name,
	'TABLE' as Level1Type, t.name collate database_default as Level1Name,
	'TRIGGER' as Level2Type, g.name collate database_default as Level2Name
from sys.tables as t
	inner join sys.triggers as g on (t.[object_id] = g.parent_id)

union all
select 4 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(t.[schema_id]) collate database_default as Level0Name,
	'TABLE' as Level1Type, t.name collate database_default as Level1Name,
	'INDEX' as Level2Type, i.name collate database_default as Level2Name
from sys.tables as t
	inner join sys.indexes as i on (t.[object_id] = i.[object_id])
where i.name is not null


union all
select 5 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(v.[schema_id]) collate database_default as Level0Name,
	'VIEW' as Level1Type, v.name collate database_default as Level1Name,
	'COLUMN' as Level2Type, c.name collate database_default as Level2Name
from sys.views as v
	inner join sys.columns as c on (v.[object_id] = c.[object_id])

union all
select 6 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(v.[schema_id]) collate database_default as Level0Name,
	'VIEW' as Level1Type, v.name collate database_default as Level1Name,
	'TRIGGER' as Level2Type, g.name collate database_default as Level2Name
from sys.views as v
	inner join sys.triggers as g on (v.[object_id] = g.parent_id)

union all
select 7 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(v.[schema_id]) collate database_default as Level0Name,
	'VIEW' as Level1Type, v.name collate database_default as Level1Name,
	'INDEX' as Level2Type, i.name collate database_default as Level2Name
from sys.views as v
	inner join sys.indexes as i on (v.[object_id] = i.[object_id])
where i.name is not null


union all
select 8 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(f.[schema_id]) collate database_default as Level0Name,
	'FUNCTION' as Level1Type, f.name collate database_default as Level1Name,
	'PARAMETER' as Level2Type, a.name collate database_default as Level2Name
from sys.objects as f
	inner join sys.parameters as a on (f.[object_id] = a.[object_id])
where f.[type]  in ('FN', ' FS', 'FT', 'IF', 'TF')

union all
select 9 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(f.[schema_id]) collate database_default as Level0Name,
	'FUNCTION' as Level1Type, f.name collate database_default as Level1Name,
	'COLUMN' as Level2Type, c.name collate database_default as Level2Name
from sys.objects as f
	inner join sys.columns as c on (f.[object_id] = c.[object_id])
where f.[type]  in ('FT', 'IF', 'TF')

union all
select 10 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(f.[schema_id]) collate database_default as Level0Name,
	'FUNCTION' as Level1Type, f.name collate database_default as Level1Name,
	'CONSTRAINT' as Level2Type, c.name collate database_default as Level2Name
from sys.objects as f
	inner join sys.objects as c on (f.[object_id] = c.parent_object_id)
where f.[type]  in ('TF') and c.[type] in ('C', 'D', 'F', 'PK', 'UQ')


union all
select 11 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(p.[schema_id]) collate database_default as Level0Name,
	'PROCEDURE' as Level1Type, p.name collate database_default as Level1Name,
	'PARAMETER' as Level2Type, a.name collate database_default as Level2Name
from sys.procedures as p
	inner join sys.parameters as a on (p.[object_id] = a.[object_id])


union all
select 12 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(q.[schema_id]) collate database_default as Level0Name,
	'QUEUE' as Level1Type, q.name collate database_default as Level1Name,
	'EVENT NOTIFICATION' as Level2Type, e.name collate database_default as Level2Name
from sys.service_queues as q
	inner join sys.objects as e on (q.[object_id] = e.parent_object_id);
GO




